package com.sohoometer.web.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.Predicate;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.nari.baseinfo.model.IBuilding;
import com.nari.baseinfo.model.ICommunity;
import com.nari.baseinfo.model.IHeatMeter;
import com.nari.baseinfo.model.IHeatRoom;
import com.nari.baseinfo.model.IProject;
import com.nari.heatshare.model.IAssignMeterDataGather;
import com.nari.heatshare.model.IHeatMeterDataGather;
import com.nari.heatshare.model.IShareAreaResult;
import com.nari.heatshare.model.ITemperaturePointDataGather;
import com.nari.heatshare.utils.excelexport.heatcharge.vo.AreaResultBillListVo;
import com.nari.heatshare.utils.excelexport.heatcharge.vo.AreaResultBillTotalVo;
import com.nari.heatshare.utils.queryvo.AssignMeterDataCustomizedQuery;
import com.nari.heatshare.utils.queryvo.HeatMeterDataRecordExtendQuery;
import com.nari.heatshare.utils.queryvo.TempPointDataCustomizedQuery;
import com.nari.interfaces.model.IHeatMeterData24Gather;
import com.nari.platform.model.ResponseMsg;
import com.sohoometer.web.bean.AssignDataWithInfo;

public class ExcelExportUtils {
	private String heatMeasureCommunityDataExcel = "HeatMeasureCommunityData.xls";
	private String heatMeasureInfoExcel = "SingleHeatMeasureInfo.xls";
	private String assignCommunityUploadData = "AssignCommunityUploadData.xls";
	private String buildingAssignMeterInfo = "BuildingAssignMeterInfo.xls";
	private String tempraturePointCommunity = "TempraturePointCommunity.xls";
	private String buildingTempraturePointData = "BuildingTempraturePointData.xls";
	private String billExport = "billExport.xls";

	public ExcelExportUtils() {
	}

	/**
	 * 加载excel模板
	 *
	 * @param path
	 *            类路径下的地址
	 */
	private HSSFWorkbook getWorkBook(String path) {
		InputStream inputStream = getClass().getResourceAsStream(path);
		if (inputStream == null) {
			throw new RuntimeException("请检查类路径下是否存在" + path);
		}
		HSSFWorkbook workbook = null;
		try {
			workbook = new HSSFWorkbook(inputStream);
		} catch (IOException e) {
			throw new RuntimeException("请确认" + path + "excel模板文件能够被解析");
		}
		return workbook;
	}

	/**
	 * 得到小区热量表数据的Excel
	 *
	 * @param community
	 *            小区实例
	 * @param project
	 *            小区所关联的项目实例
	 * @param recordTime
	 *            对应的抄表时间
	 * @param heatMeterDataList
	 *            对应小区的所有热量表数据
	 */
	public HSSFWorkbook getHeatMeasureCommunityWorkbook(ICommunity community,
			IProject project, Date recordTime,
			List<IHeatMeterDataGather> heatMeterDataList,
			Map<String, HeatMeterDataRecordExtendQuery> hmdRecordExtendMap) {
		HSSFWorkbook workBook = getWorkBook(heatMeasureCommunityDataExcel);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 设置项目名称 cell 0,1
		HSSFRow projectNamerow = sheet.getRow(0);
		HSSFCell projectNameCell = projectNamerow.getCell(1);
		projectNameCell.setCellValue(project.getProjectName());
		// 设置小区名称 cell 1,1
		HSSFRow communityRow = sheet.getRow(1);
		HSSFCell communityCell = communityRow.getCell(1);
		communityCell.setCellValue(community.getCommunityName());
		// 设置抄表日期 cell 2,1 cellType 为date类型
		HSSFRow recordTimeRow = sheet.getRow(2);
		HSSFCell recordTimeCell = recordTimeRow.getCell(1);
		recordTimeCell.setCellValue(recordTime);
		// 填充热量表数据
		if (heatMeterDataList == null || heatMeterDataList.size() < 1) {
			return workBook;
		}
		int j = 4; // 从第四行进行填充
		for (int i = 0; i < heatMeterDataList.size(); i++) {
			IHeatMeterDataGather heatMeterDataGather = heatMeterDataList.get(i);
			// 创建相应的行
			HSSFRow createRow = sheet.createRow(j++);
			// 添加序号
			HSSFCell numberCell = createRow.createCell(0,
					HSSFCell.CELL_TYPE_STRING);
			numberCell.setCellValue((i + 1) + "");
			// 添加热量表号
			String meterNo = heatMeterDataGather.getMeterNo();
			HSSFCell metroNoCell = createRow.createCell(1,
					HSSFCell.CELL_TYPE_STRING);
			if (StringUtils.isNotBlank(meterNo)) {
				metroNoCell.setCellValue(meterNo);
			}
			// 添加所属楼栋
			HeatMeterDataRecordExtendQuery heatMeterDataRecordExtendQuery = hmdRecordExtendMap
					.get(heatMeterDataGather.getId());
			HSSFCell buildingCodeCell = createRow.createCell(2,
					HSSFCell.CELL_TYPE_STRING);
			HSSFCell buildingAreaCell = createRow.createCell(3,
					HSSFCell.CELL_TYPE_STRING);
			if (heatMeterDataRecordExtendQuery != null) {
				String buildingCode = heatMeterDataRecordExtendQuery
						.getBuildingCode();
				if (StringUtils.isNotBlank(buildingCode)) {
					buildingCodeCell.setCellValue(buildingCode);
				}
				// 添加楼栋面积
				double buildingArea = heatMeterDataRecordExtendQuery
						.getBuildingArea();
				buildingAreaCell.setCellValue(buildingArea);
			}
			// 累计热量
			HSSFCell sumHeatCell = createRow.createCell(4,
					HSSFCell.CELL_TYPE_STRING);
			String sumHeatFullname = HeatMeterGatherUtils
					.getSumHeatFullname(heatMeterDataGather);
			sumHeatCell.setCellValue(sumHeatFullname);
			// 累计流量
			HSSFCell sumFlowCell = createRow.createCell(5,
					HSSFCell.CELL_TYPE_STRING);
			String sumFlowFullname = HeatMeterGatherUtils
					.getSumFlowFullname(heatMeterDataGather);
			sumFlowCell.setCellValue(sumFlowFullname);
			// 瞬时流量
			HSSFCell instantFlowCell = createRow.createCell(6,
					HSSFCell.CELL_TYPE_STRING);
			String instantFlowFullname = HeatMeterGatherUtils
					.getInstantFlowFullname(heatMeterDataGather);
			instantFlowCell.setCellValue(instantFlowFullname);
			// 瞬时功率
			HSSFCell instantPower = createRow.createCell(7,
					HSSFCell.CELL_TYPE_STRING);
			instantPower.setCellValue(heatMeterDataGather.getInstantPower()
					+ "");
			//供水温度
			HSSFCell supplyTemperatureCell = createRow.createCell(8,HSSFCell.CELL_TYPE_STRING);
			double supplyTemperature = heatMeterDataGather.getSupplyTemperature();
			supplyTemperatureCell.setCellValue(supplyTemperature+"");
			//回水温度
			HSSFCell backTemperatureCell = createRow.createCell(9, HSSFCell.CELL_TYPE_STRING);
			double backTemperature = heatMeterDataGather.getBackTemperature();
			backTemperatureCell.setCellValue(backTemperature+"");
			// 温差
			HSSFCell temperatureDiffCell = createRow.createCell(10,
					HSSFCell.CELL_TYPE_STRING);
			double temperatureDiff = heatMeterDataGather.getTemperatureDiff();
			temperatureDiffCell.setCellValue(temperatureDiff + "");
			// 抄表时间
			HSSFCell recordTimeDetailCell = createRow.createCell(11);
			HSSFCellStyle createCellStyle = workBook.createCellStyle();
			HSSFDataFormat dataFormat = workBook.createDataFormat();
			short format = dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
			createCellStyle.setDataFormat(format);
			Date recordTimeDetail = heatMeterDataGather.getRecordTime();
			if (recordTimeDetail != null) {
				recordTimeDetailCell.setCellValue(recordTimeDetail);
				recordTimeDetailCell.setCellStyle(createCellStyle);
			}
		}
		return workBook;
	}

	/**
	 * 得到单个热量表数据的Excel
	 */
	public HSSFWorkbook getHeatMeasureInfoWorkbook(
			IHeatMeterDataGather dataGather, IHeatMeter heatMeter,
			IProject project, ICommunity community, IBuilding building,
			List<IHeatMeterData24Gather> meterDataList4Statis) {
		HSSFWorkbook workBook = getWorkBook(heatMeasureInfoExcel);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 第一行
		HSSFRow row1 = sheet.getRow(1);
		// 设置所属项目
		if (project != null) {
			String projectName = project.getProjectName();
			HSSFCell projectNameCell = row1.getCell(2);
			if (StringUtils.isNotBlank(projectName)) {
				projectNameCell.setCellValue(projectName);
			}
		}
		// 设置所属小区
		if (community != null) {
			String communityName = community.getCommunityName();
			HSSFCell communityNameCell = row1.getCell(4);
			if (StringUtils.isNotBlank(communityName)) {
				communityNameCell.setCellValue(communityName);
			}
		}
		if (building != null) {
			String buildingName = building.getBuildingID();
			HSSFCell buildingNameCell = row1.getCell(6);
			if (StringUtils.isNotBlank(buildingName)) {
				buildingNameCell.setCellValue(buildingName);
			}
		}
		// 分摊域相关(待定)
		// 热量表相关
		// 第二行数据
		HSSFRow row2 = sheet.getRow(2);
		if (dataGather != null) {
			// 热量表表号
			HSSFCell heatNoCell = row2.getCell(2);
			String meterNo = dataGather.getMeterNo();
			if (StringUtils.isNotBlank(meterNo)) {
				heatNoCell.setCellValue(meterNo);
			}
			// 抄表时间
			HSSFCell recordTimeCell = row2.getCell(4);
			Date recordTime = dataGather.getRecordTime();
			if (recordTime != null) {
				recordTimeCell.setCellValue(recordTime);
			}
		}
		// 第三行数据
		HSSFRow row3 = sheet.getRow(3);
		// 热表的型号,热表口径,管道口径
		if (heatMeter != null) {
			HSSFCell heatTypeCell = row3.getCell(2);
			String meterType = heatMeter.getMeterType();
			if (StringUtils.isNotBlank(meterType)) {
				heatTypeCell.setCellValue(meterType);
			}
			// 热表口径
			HSSFCell meterDiameterCell = row3.getCell(4);
			double meterDiameterDouble = heatMeter.getMeterDiameter();
			String meterDiameter = "";
			if (meterDiameterDouble != 0) {
				meterDiameter = meterDiameterDouble + "";
			}
			if (StringUtils.isNotBlank(meterDiameter)) {
				meterDiameterCell.setCellValue(meterDiameter);
			}

			// 管道口径
			HSSFCell pipeDiameterCell = row3.getCell(6);
			double pipeDiameterDouble = heatMeter.getPipeDiameter();
			String pipeDiameter = "";
			if (pipeDiameterDouble != 0) {
				pipeDiameter = heatMeter.getPipeDiameter() + "";
			}
			if (StringUtils.isNotBlank(pipeDiameter)) {
				pipeDiameterCell.setCellValue(pipeDiameter);
			}
		}
		// 第四行
		HSSFRow row4 = sheet.getRow(4);
		// 累计热量
		HSSFCell sumHeatCell = row4.getCell(2);
		String sumHeatFullname = HeatMeterGatherUtils
				.getSumHeatFullname(dataGather);
		sumHeatCell.setCellValue(sumHeatFullname);
		// 累计流量
		HSSFCell sumFlowCell = row4.getCell(4);
		String sumFlowFullname = HeatMeterGatherUtils
				.getSumFlowFullname(dataGather);
		sumFlowCell.setCellValue(sumFlowFullname);
		// 瞬时流量
		HSSFCell instantFlowCell = row4.getCell(6);
		String instantFlowFullname = HeatMeterGatherUtils
				.getInstantFlowFullname(dataGather);
		instantFlowCell.setCellValue(instantFlowFullname);
		// 第五行
		HSSFRow row5 = sheet.getRow(5);
		// 结算日热量
		HSSFCell settleAccountHeatCell = row5.getCell(2);
		String settleAccountHeatFullname = HeatMeterGatherUtils
				.getsettleAccountHeatFullname(dataGather);
		settleAccountHeatCell.setCellValue(settleAccountHeatFullname);
		// 热功率
		HSSFCell heatPowerCell = row5.getCell(4);
		String heatPowerFullname = HeatMeterGatherUtils
				.getHeatPowerFullname(dataGather);
		heatPowerCell.setCellValue(heatPowerFullname);
		// 瞬时功率
		HSSFCell instantPowercell = row5.getCell(6);
		double instantPower = 0;
		if (dataGather != null) {
			instantPower = dataGather.getInstantPower();
		}
		if (instantPower != 0) {
			instantPowercell.setCellValue(instantPower + "kW");
		}

		// 第六行
		HSSFRow row6 = sheet.getRow(6);
		// 供水温度
		HSSFCell supplyTemperatureCell = row6.getCell(2);
		double supplyTemperature = 0;
		if (dataGather != null) {
			supplyTemperature = dataGather.getSupplyTemperature();
		}
		if (supplyTemperature != 0) {
			supplyTemperatureCell.setCellValue(supplyTemperature + "℃");
		}
		// 回水温度
		HSSFCell backTemperatureCell = row6.getCell(4);
		double backTemperature = 0;
		if (dataGather != null) {
			backTemperature = dataGather.getBackTemperature();
		}
		if (backTemperature != 0) {
			backTemperatureCell.setCellValue(backTemperature + "℃");
		}
		// 供回水温差
		HSSFCell temperatureDiffCell = row6.getCell(6);
		double temperatureDiff = 0;
		if (dataGather != null) {
			temperatureDiff = dataGather.getTemperatureDiff();
		}
		if (temperatureDiff != 0) {
			temperatureDiffCell.setCellValue(temperatureDiff + "℃");
		}
		// 第七行
		HSSFRow row7 = sheet.getRow(7);
		// 累计运行时间

		HSSFCell runHoursCell = row7.getCell(2);
		double runHours = 0;
		if (dataGather != null) {
			runHours = dataGather.getRunHours();
		}
		if (runHours != 0) {
			runHoursCell.setCellValue(runHours + "h");
		}
		// 累计出错时间
		HSSFCell wrongHoursCell = row7.getCell(4);
		double wrongHours = 0;
		if (dataGather != null) {
			wrongHours = dataGather.getWrongHours();
		}
		if (wrongHours != 0) {
			wrongHoursCell.setCellValue(wrongHours + "h");
		}
		// 故障信息
		HSSFCell wrongInfoCell = row7.getCell(6);
		String wrongInfo = "";
		if (dataGather != null) {
			wrongInfo = dataGather.getWrongInfo();
		}
		if (StringUtils.isNotBlank(wrongInfo)) {
			wrongInfoCell.setCellValue(wrongInfo + "");
		}
		// 遍历热量表数据,得到的量表中的详细数据。
		if (meterDataList4Statis == null || meterDataList4Statis.size() < 1) {
			return workBook;
		}// 如果热量表数据没有查到,则直接返回上述数据

		// 热量表详细数据
		int j = 9; // 偏移量
		for (int i = 0; i < meterDataList4Statis.size(); i++) {
			// 创建行
			HSSFRow heatMeterRow = sheet.createRow(i + j);
			IHeatMeterData24Gather heatMeterData24Gather = meterDataList4Statis
					.get(i);
			if (heatMeterData24Gather == null) {
				continue;
			}
			// 序号
			HSSFCell numCell = heatMeterRow.createCell(0,
					HSSFCell.CELL_TYPE_STRING);
			numCell.setCellValue((i + 8) + ""); // 设置起始序号

			// 热量表表号
			HSSFCell meterNoCell = heatMeterRow.createCell(1,
					HSSFCell.CELL_TYPE_STRING);
			String meterNo = heatMeterData24Gather.getMeterNo();
			meterNoCell.setCellValue(meterNo);
			// 累计热量
			HSSFCell sumHeatFullname2tCell = heatMeterRow.createCell(2,
					HSSFCell.CELL_TYPE_STRING);
			String sumHeatFullname2 = HeatMeterData24GatherUtils
					.getSumHeatFullname(heatMeterData24Gather);
			sumHeatFullname2tCell.setCellValue(sumHeatFullname2);
			// 累计流量
			HSSFCell sumFlowFullname2Cell = heatMeterRow.createCell(3,
					HSSFCell.CELL_TYPE_STRING);
			String sumFlowFullname2 = HeatMeterData24GatherUtils
					.getSumFlowFullname(heatMeterData24Gather);
			sumFlowFullname2Cell.setCellValue(sumFlowFullname2);
			// 供水温度
			HSSFCell supplyTemperatureCell2 = heatMeterRow.createCell(4,
					HSSFCell.CELL_TYPE_STRING);
			double supplyTemperature2 = 0;

			supplyTemperature2 = heatMeterData24Gather.getSupplyTemperature();

			supplyTemperatureCell2.setCellValue(supplyTemperature2 + "℃");

			// 回水温度
			HSSFCell backTemperatureCell2 = heatMeterRow.createCell(5,
					HSSFCell.CELL_TYPE_STRING);
			double backTemperature2 = 0;

			backTemperature2 = heatMeterData24Gather.getBackTemperature();

			if (backTemperature2 != 0) {
				backTemperatureCell2.setCellValue(backTemperature2 + "℃");
			}
			// 温差
			HSSFCell temperatureDiffCell2 = heatMeterRow.createCell(6,
					HSSFCell.CELL_TYPE_STRING);
			double temperatureDiff2 = 0;

			temperatureDiff2 = heatMeterData24Gather.getTemperatureDiff();

			if (temperatureDiff2 != 0) {
				temperatureDiffCell2.setCellValue(temperatureDiff2 + "℃");
			}
			// 抄表时间
			HSSFCell recordTimeCell = heatMeterRow.createCell(7);
			HSSFCellStyle createCellStyle = workBook.createCellStyle();
			HSSFDataFormat dataFormat = workBook.createDataFormat();
			short format = dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
			createCellStyle.setDataFormat(format);
			recordTimeCell.setCellStyle(createCellStyle);
			if (heatMeterData24Gather != null) {
				Date recordTime = heatMeterData24Gather.getRecordTime();
				if (recordTime != null) {
					recordTimeCell.setCellValue(recordTime);
				}
			}
			// 整个Excel设计完毕
		}
		return workBook;
	}

	/**
	 * 取得分配表小区整体上传率信息的Excel
	 * */

	public HSSFWorkbook getAssignCommunityUploadData(IProject project,
			ICommunity community, Date recordTimeStart, Date recordTimeEnd,
			List<AssignMeterDataCustomizedQuery> assignMeterDatas,
			Map<String, String> projectMap, Map<String, String> communityMap,
			Map<String, String> buildingMap) {
		HSSFWorkbook workBook = getWorkBook(assignCommunityUploadData);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 项目名称
		HSSFRow row1 = sheet.getRow(0);
		HSSFCell projectNameCell = row1.getCell(1);
		if (project != null) {
			String projectName = project.getProjectName();
			projectNameCell.setCellValue(projectName);
		}
		// 设置所属小区
		HSSFRow row2 = sheet.getRow(1);
		HSSFCell communitytNameCell = row2.getCell(1);
		if (community != null) {
			String communityName = community.getCommunityName();
			communitytNameCell.setCellValue(communityName);
		}
		// 抄表时间
		HSSFRow row3 = sheet.getRow(2);
		String recordTime = "";
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		if (recordTimeStart != null) {
			String startTime = dateFormat.format(recordTimeStart);
			recordTime += startTime;
		}
		if (recordTimeEnd != null) {
			String endTime = dateFormat.format(recordTimeEnd);
			recordTime = recordTime + "     ----     " + endTime;
		}
		HSSFCell recordTimeCell = row3.getCell(1);
		recordTimeCell.setCellValue(recordTime);
		// 分配表小区整体上传率
		if (assignMeterDatas == null || assignMeterDatas.size() < 1) {
			return workBook;
		}
		int j = 4; // 对于excel模板的偏移量
		for (int i = 0; i < assignMeterDatas.size(); i++) {
			AssignMeterDataCustomizedQuery meterDataCustomizedQuery = assignMeterDatas
					.get(i);
			HSSFRow assignMeterRow = sheet.createRow(i + j);
			// 序号
			HSSFCell numberCell = assignMeterRow.createCell(0,
					HSSFCell.CELL_TYPE_STRING);
			numberCell.setCellValue((i + 1) + "");
			// 所属项目
			HSSFCell projectCell = assignMeterRow.createCell(1,
					HSSFCell.CELL_TYPE_STRING);
			String projectUID = meterDataCustomizedQuery.getProjectUID();
			if (StringUtils.isNotBlank(projectUID)) {
				String pname = projectMap.get(projectUID);
				projectCell.setCellValue(pname);
			}
			// 所属小区
			HSSFCell communityCell = assignMeterRow.createCell(2,
					HSSFCell.CELL_TYPE_STRING);
			String communityUID = meterDataCustomizedQuery.getCommunityUID();
			if (StringUtils.isNotBlank(communityUID)) {
				String cname = communityMap.get(communityUID);
				communityCell.setCellValue(cname);
			}
			// 所属楼栋
			HSSFCell buildingCell = assignMeterRow.createCell(3,
					HSSFCell.CELL_TYPE_STRING);
			String buildingUID = meterDataCustomizedQuery.getBuildingUID();
			if (StringUtils.isNotBlank(buildingUID)) {
				String bname = buildingMap.get(buildingUID);
				buildingCell.setCellValue(bname);
			}
			// 分配表数
			HSSFCell assignMeterCell = assignMeterRow.createCell(4,
					HSSFCell.CELL_TYPE_STRING);
			int assignMeterNum = meterDataCustomizedQuery.getAssignMeterNum();
			if (assignMeterNum != 0) {
				assignMeterCell.setCellValue(assignMeterNum + "");
			}
			// 成功上传数
			HSSFCell successUploadCounterCell = assignMeterRow.createCell(5,
					HSSFCell.CELL_TYPE_STRING);
			int successUploadCounter = meterDataCustomizedQuery
					.getSuccessUploadCounter();
			if (successUploadCounter != 0) {
				successUploadCounterCell
						.setCellValue(successUploadCounter + "");
			}
			// 上传率
			HSSFCell uploadPercentCell = assignMeterRow.createCell(6,
					HSSFCell.CELL_TYPE_STRING);
			double uploadPercent = meterDataCustomizedQuery.getUploadPercent();
			if (uploadPercent != 0) {
				uploadPercentCell.setCellValue(uploadPercent + "");
			}
			// 上传日期

			HSSFCell uploadDateCell = assignMeterRow.createCell(7,
					HSSFCell.CELL_TYPE_NUMERIC);
			// 创建日期格式
			HSSFCellStyle createCellStyle = workBook.createCellStyle();
			HSSFDataFormat dataFormat = workBook.createDataFormat();
			short format = dataFormat.getFormat("yyyy-MM-dd");
			createCellStyle.setDataFormat(format);
			// 创建日期格式结束
			uploadDateCell.setCellStyle(createCellStyle);
			Date time = meterDataCustomizedQuery.getReadTime();
			uploadDateCell.setCellValue(time);
		}
		return workBook;
	}

	/**
	 * 楼栋分配表信息
	 * */
	public HSSFWorkbook getBuildingAssignMeterInfoWorkBook(IProject project,
			ICommunity community, IBuilding building,
			AssignMeterDataCustomizedQuery amdDataCustomizedQuery,
			List<IAssignMeterDataGather> assignMeterDataGathers) {
		HSSFWorkbook workBook = getWorkBook(buildingAssignMeterInfo);
		// 设置抄表时间格式
		HSSFCellStyle createCellStyle = workBook.createCellStyle();
		HSSFDataFormat dataFormat = workBook.createDataFormat();
		short format = dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
		createCellStyle.setDataFormat(format);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 第一行
		HSSFRow row1 = sheet.getRow(0);
		HSSFCell projectNameCell = row1.getCell(1);
		if (project != null) {
			String projectName = project.getProjectName();
			projectNameCell.setCellValue(projectName);
		}
		HSSFRow row2 = sheet.getRow(1);
		HSSFCell communityNameCell = row2.getCell(1);
		if (community != null) {
			String communityName = community.getCommunityName();
			communityNameCell.setCellValue(communityName);
		}
		HSSFRow row3 = sheet.getRow(2);
		HSSFCell buildingNameCell = row3.getCell(1);
		if (building != null) {
			String buildingName = building.getBuildingName();
			buildingNameCell.setCellValue(buildingName);
		}
		// 第四行 查询分配表数据
		HSSFRow row4 = sheet.getRow(3);
		if (amdDataCustomizedQuery != null) {
			// 分配表数
			HSSFCell assignMeterNo = row4.getCell(1);
			assignMeterNo.setCellValue(amdDataCustomizedQuery
					.getAssignMeterDataCounter());
			// 成功上传数
			HSSFCell successUpload = row4.getCell(3);
			successUpload.setCellValue(amdDataCustomizedQuery
					.getSuccessUploadCounter());
			// 上传率
			HSSFCell uploadPercentage = row4.getCell(5);
			uploadPercentage.setCellValue(amdDataCustomizedQuery
					.getUploadPercent() + "%");
			// 上传时间
			HSSFCell uploadDate = row4.getCell(7);
			uploadDate.setCellStyle(createCellStyle);
			uploadDate.setCellValue(amdDataCustomizedQuery.getReadTime());
		}
		// 分配表查询相关信息
		if (assignMeterDataGathers == null || assignMeterDataGathers.size() < 1) {
			return workBook;
		}
		int j = 5;
		for (int i = 0; i < assignMeterDataGathers.size(); i++) {
			IAssignMeterDataGather assignMeterDataGather = assignMeterDataGathers
					.get(i);
			HSSFRow assignMeterRow = sheet.createRow(i + j);
			// 序号
			HSSFCell numberCell = assignMeterRow.createCell(0);
			numberCell.setCellValue(i + 1);
			// 分配表表号
			HSSFCell assignMeterNoCell = assignMeterRow.createCell(1);
			String seriousNum = assignMeterDataGather.getSeriousNum();
			assignMeterNoCell.setCellValue(seriousNum);
			// 集中器号
			HSSFCell concentratorNoCell = assignMeterRow.createCell(2);
			String concentratorNo = assignMeterDataGather.getConcentratorNo();
			concentratorNoCell.setCellValue(concentratorNo);
			// 读数
			HSSFCell readNumCell = assignMeterRow.createCell(3);
			double readNum = assignMeterDataGather.getReadNum();
			if (readNum != 0) {
				readNumCell.setCellValue(readNum);
			}
			// 去年读数 (去年热量)
			HSSFCell lastYearHeatCell = assignMeterRow.createCell(4);
			double lastYearHeat = assignMeterDataGather.getLastYearHeat();
			lastYearHeatCell.setCellValue(lastYearHeat + "");
			// 背板温度
			HSSFCell backTmperatureCell = assignMeterRow.createCell(5);
			double backTmperature = assignMeterDataGather.getBackTmperature();
			if (backTmperature != 0) {
				backTmperatureCell.setCellValue(backTmperature);
			}
			// 前板温度
			HSSFCell fontTemperatureCell = assignMeterRow.createCell(6);
			double fontTemperature = assignMeterDataGather.getFontTemperature();
			fontTemperatureCell.setCellValue(fontTemperature);
			// 抄表时间
			HSSFCell recordTimeCell = assignMeterRow.createCell(7);
			recordTimeCell.setCellStyle(createCellStyle);
			Date recordTime = assignMeterDataGather.getRecordTime();
			recordTimeCell.setCellValue(recordTime);
		}
		return workBook;
	}

	
	/**
	 * 楼栋分配表信息
	 * */
	public HSSFWorkbook getBuildingAssignMeterInfoWorkBook(IProject project,
			ICommunity community, IBuilding building,
			List<AssignDataWithInfo>  assignmeterInfos,
			AssignMeterDataCustomizedQuery amdDataCustomizedQuery) {

		HSSFWorkbook workBook = getWorkBook(buildingAssignMeterInfo);
		// 设置抄表时间格式
		HSSFCellStyle createCellStyle = workBook.createCellStyle();
		HSSFDataFormat dataFormat = workBook.createDataFormat();
		short format = dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
		createCellStyle.setDataFormat(format);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 第一行
		HSSFRow row1 = sheet.getRow(0);
		HSSFCell projectNameCell = row1.getCell(1);
		if (project != null) {
			String projectName = project.getProjectName();
			projectNameCell.setCellValue(projectName);
		}
		HSSFRow row2 = sheet.getRow(1);
		HSSFCell communityNameCell = row2.getCell(1);
		if (community != null) {
			String communityName = community.getCommunityName();
			communityNameCell.setCellValue(communityName);
		}
		HSSFRow row3 = sheet.getRow(2);
		HSSFCell buildingNameCell = row3.getCell(1);
		if (building != null) {
			String buildingName = building.getBuildingName();
			buildingNameCell.setCellValue(buildingName);
		}
		// 第四行 查询分配表数据
		HSSFRow row4 = sheet.getRow(3);
		if (amdDataCustomizedQuery != null) {
			// 分配表数
			HSSFCell assignMeterNo = row4.getCell(1);
			assignMeterNo.setCellValue(amdDataCustomizedQuery
					.getAssignMeterDataCounter());
			// 成功上传数
			HSSFCell successUpload = row4.getCell(3);
			successUpload.setCellValue(amdDataCustomizedQuery
					.getSuccessUploadCounter());
			// 上传率
			HSSFCell uploadPercentage = row4.getCell(5);
			uploadPercentage.setCellValue(amdDataCustomizedQuery
					.getUploadPercent() + "%");
			// 上传时间
			HSSFCell uploadDate = row4.getCell(7);
			uploadDate.setCellStyle(createCellStyle);
			uploadDate.setCellValue(amdDataCustomizedQuery.getReadTime());
		}
		// 分配表查询相关信息
		if (assignmeterInfos == null || assignmeterInfos.size() < 1) {
			return workBook;
		}
		int j = 5;
		for (int i = 0; i < assignmeterInfos.size(); i++) {
			IAssignMeterDataGather assignMeterDataGather = assignmeterInfos.get(i).getData();
			IHeatRoom heatRoom = assignmeterInfos.get(i).getHeatRoom();
			HSSFRow assignMeterRow = sheet.createRow(i + j);
			// 序号
			HSSFCell numberCell = assignMeterRow.createCell(0);
			numberCell.setCellValue(i + 1);
			//门牌号
			HSSFCell houseNoCell = assignMeterRow.createCell(1);
			if(heatRoom!=null&&heatRoom.getHouseNo()!=null) {
			houseNoCell.setCellValue(heatRoom.getHouseNo());
			}
			// 分配表表号
			HSSFCell assignMeterNoCell = assignMeterRow.createCell(2);
			String seriousNum = assignMeterDataGather.getSeriousNum();
			assignMeterNoCell.setCellValue(seriousNum);
			// 集中器号
			HSSFCell concentratorNoCell = assignMeterRow.createCell(3);
			String concentratorNo = assignMeterDataGather.getConcentratorNo();
			concentratorNoCell.setCellValue(concentratorNo);
			// 读数
			HSSFCell readNumCell = assignMeterRow.createCell(4);
			double readNum = assignMeterDataGather.getReadNum();
			if (readNum != 0) {
				readNumCell.setCellValue(readNum);
			}
			// 去年读数 (去年热量)
			HSSFCell lastYearHeatCell = assignMeterRow.createCell(5);
			double lastYearHeat = assignMeterDataGather.getLastYearHeat();
			lastYearHeatCell.setCellValue(lastYearHeat + "");
			// 背板温度
			HSSFCell backTmperatureCell = assignMeterRow.createCell(6);
			double backTmperature = assignMeterDataGather.getBackTmperature();
			if (backTmperature != 0) {
				backTmperatureCell.setCellValue(backTmperature);
			}
			// 前板温度
			HSSFCell fontTemperatureCell = assignMeterRow.createCell(7);
			double fontTemperature = assignMeterDataGather.getFontTemperature();
			fontTemperatureCell.setCellValue(fontTemperature);
			// 抄表时间
			HSSFCell recordTimeCell = assignMeterRow.createCell(8);
			recordTimeCell.setCellStyle(createCellStyle);
//			Date recordTime = assignMeterDataGather.getRecordTime();
			Date readTime = assignMeterDataGather.getReadTime();
			if(readTime==null) continue;
			recordTimeCell.setCellValue(readTime);
		}
		return workBook;
	}
	/**
	 * 温度点小区整体上传信息
	 * 
	 * @param project
	 *            工程的查询名称
	 * @param community
	 *            社区的查询名称
	 * @param building
	 *            楼栋的查询名称
	 * @param startDate
	 *            查询起始时间
	 * @param endDate
	 *            查询终止时间
	 * @param tempPointDataCustomizedQueries
	 *            查询的温度点列表
	 * */
	public HSSFWorkbook getTempraturePointCommunityWorkBook(IProject project,
			ICommunity community, IBuilding building, Date startDate,
			Date endDate,
			List<TempPointDataCustomizedQuery> tempPointDataCustomizedQueries,
			Map<String, String> pageProjectMap,
			Map<String, String> pageCommunityMap,
			Map<String, String> pageBuildingMap) {
		HSSFWorkbook workBook = getWorkBook(tempraturePointCommunity);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 第一行 所属项目
		HSSFRow row1 = sheet.getRow(0);
		HSSFCell projectNameCell = row1.getCell(1);
		if (project != null) {
			String projectName = project.getProjectName();
			projectNameCell.setCellValue(projectName);
		}
		// 第二行 所属小区
		HSSFRow row2 = sheet.getRow(1);
		HSSFCell communityNameCell = row2.getCell(1);
		if (community != null) {
			String communityName = community.getCommunityName();
			communityNameCell.setCellValue(communityName);
		}
		// 第三行 抄表时间
		HSSFRow row3 = sheet.getRow(2);
		HSSFCell recordTimeCell = row3.getCell(1);
		String recordTime = "";
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		if (startDate != null) {
			String startDateString = dateFormat.format(startDate);
			recordTime += startDateString;
			recordTime += "    -    ";
		}
		if (endDate != null) {
			String endDateString = dateFormat.format(endDate);
			recordTime += endDateString;
		}
		recordTimeCell.setCellValue(recordTime);
		// 温度点详细信息
		if (tempPointDataCustomizedQueries == null
				|| tempPointDataCustomizedQueries.size() < 1) {
			return workBook;
		}
		int j = 4;
		for (int i = 0; i < tempPointDataCustomizedQueries.size(); i++) {
			TempPointDataCustomizedQuery tempPointDataCustomizedQuery = tempPointDataCustomizedQueries
					.get(i);
			HSSFRow temPointTempratureRow = sheet.createRow(j + i);
			// 序号
			HSSFCell numCell = temPointTempratureRow.createCell(0,
					HSSFCell.CELL_TYPE_STRING);
			numCell.setCellValue(i + 1);
			// 所属项目
			HSSFCell projectCell = temPointTempratureRow.createCell(1,
					HSSFCell.CELL_TYPE_STRING);
			String projectUID = tempPointDataCustomizedQuery.getProjectUID();
			if (StringUtils.isNotBlank(projectUID)) {
				if (pageProjectMap != null) {
					String projectNameMap = pageProjectMap.get(projectUID);
					projectCell.setCellValue(projectNameMap);
				}
			}
			// 所属小区
			HSSFCell communityCell = temPointTempratureRow.createCell(2,
					HSSFCell.CELL_TYPE_STRING);
			String communityUID = tempPointDataCustomizedQuery
					.getCommunityUID();
			if (StringUtils.isNotBlank(communityUID)) {
				if (pageCommunityMap != null) {
					String communityNameMap = pageCommunityMap
							.get(communityUID);
					communityCell.setCellValue(communityNameMap);
				}
			}
			// 所属楼栋
			HSSFCell buildingCell = temPointTempratureRow.createCell(3,
					HSSFCell.CELL_TYPE_STRING);
			String buildingUID = tempPointDataCustomizedQuery.getBuildingUID();
			if (StringUtils.isNotBlank(buildingUID)) {
				if (pageBuildingMap != null) {
					String buildingNameMap = pageBuildingMap.get(buildingUID);
					buildingCell.setCellValue(buildingNameMap);
				}
			}
			// 温度点数
			HSSFCell temPointNumCell = temPointTempratureRow.createCell(4,
					HSSFCell.CELL_TYPE_STRING);
			int tempPointNum = tempPointDataCustomizedQuery.getTempPointNum();
			if (tempPointNum != 0) {
				temPointNumCell.setCellValue(tempPointNum + "");
			}
			// 抄表日期
			HSSFCell detailRecordTimeCell = temPointTempratureRow.createCell(5,
					HSSFCell.CELL_TYPE_STRING);
			String recordDate = tempPointDataCustomizedQuery.getRecordDate();
			detailRecordTimeCell.setCellValue(recordDate);
			// 记录数
			HSSFCell temPointDataCell = temPointTempratureRow.createCell(6,
					HSSFCell.CELL_TYPE_STRING);
			int tempPointDataCounter = tempPointDataCustomizedQuery
					.getTempPointDataCounter();
			if (tempPointDataCounter != 0) {
				temPointDataCell.setCellValue(tempPointDataCounter + "");
			}
		}
		return workBook;
	}

	/**
	 * 楼栋温度点统计Excel
	 * 
	 * @param tempratureCount
	 *            温度点数
	 * @param tempPointDataCounter
	 *            上传数
	 * @param recordTime
	 *            上传日期
	 * */
	public HSSFWorkbook getBuildingTempraturePointDataWorkBook(
			IProject project, ICommunity community, IBuilding building,
			List<ITemperaturePointDataGather> temperaturePointDataGathers,
			Map<String, String> houseNoMaping, Date recordTime,
			int tempratureCount, int tempPointDataCounter) {
		HSSFWorkbook workBook = getWorkBook(buildingTempraturePointData);
		HSSFSheet sheet = workBook.getSheetAt(0);
		// 所属项目
		HSSFRow row1 = sheet.getRow(0);
		HSSFCell projectNameCell = row1.getCell(1);
		if (project != null) {
			projectNameCell.setCellValue(project.getProjectName());
		}
		// 所属小区
		HSSFRow row2 = sheet.getRow(1);
		HSSFCell communityNameCell = row2.getCell(1);
		if (community != null) {
			communityNameCell.setCellValue(community.getCommunityName());
		}
		// 所属楼栋
		HSSFRow row3 = sheet.getRow(2);
		HSSFCell buildingNameCell = row3.getCell(1);
		if (building != null) {
			buildingNameCell.setCellValue(building.getBuildingName());
		}
		// 第四行
		HSSFRow row4 = sheet.getRow(3);
		// 温度点数
		HSSFCell tempretureNoCell = row4.getCell(1);
		if (tempratureCount != 0) {
			tempretureNoCell.setCellValue(tempratureCount);
		}
		// 上传数
		HSSFCell tempPointDataCounterCell = row4.getCell(3);
		if (tempPointDataCounter != 0) {
			tempPointDataCounterCell.setCellValue(tempPointDataCounter);
		}
		// 抄表时间
		HSSFCell recordTimeCell = row4.getCell(5);
		HSSFCellStyle createCellStyle = workBook.createCellStyle();
		HSSFDataFormat dataFormat = workBook.createDataFormat();
		short format = dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
		createCellStyle.setDataFormat(format);
		recordTimeCell.setCellStyle(createCellStyle);
		recordTimeCell.setCellValue(recordTime);
		// 温度点24小时采集数据
		if (temperaturePointDataGathers == null
				|| temperaturePointDataGathers.size() < 1) {
			return workBook;
		}
		int j = 5;// Excel的偏移量
		for (int i = 0; i < temperaturePointDataGathers.size(); i++) {
			ITemperaturePointDataGather temperaturePointDataGather = temperaturePointDataGathers
					.get(i);
			HSSFRow tempPointDataRow = sheet.createRow(i + j);
			// 设置温度点的序列号
			HSSFCell tempratureSeriaNoCell = tempPointDataRow.createCell(0);
			String concentratorNo = temperaturePointDataGather
					.getConcentratorNo();
			String seriousNum = temperaturePointDataGather.getSeriousNum();
			String key = concentratorNo + "-" + seriousNum;
			String temp = houseNoMaping.get(key);
			String value = "[" + temp + "]" + " " + seriousNum;
			tempratureSeriaNoCell.setCellValue(value);
			// 设置每个时间的数值。
			// 设置0小时
			HSSFCell hour0Cell = tempPointDataRow.createCell(1);
			Double hour0Value = temperaturePointDataGather.getHour0Value();
			if (hour0Value != null && hour0Value.floatValue() != 0) {
				hour0Cell.setCellValue(hour0Value);
			}
			// 该用反射做的,但是没心思，凑合看吧 以后也好拓展 靠 上反射
			for (int h = 0; h < 24; h++) {
				String methodName = "getHour" + h + "Value";
				try {
					Method method = ITemperaturePointDataGather.class
							.getMethod(methodName);
					Double hourValue = (Double) method
							.invoke(temperaturePointDataGather);
					HSSFCell hourCell = tempPointDataRow.createCell(h + 1);
					if (hourValue != null && hourValue.floatValue() != 0) {
						hourCell.setCellValue(hourValue);
					}
				} catch (Exception e) {
					System.out.println("代码写错了，模型和方法的名称对不上");
				}
			}// for 语句 hour 从此结束
		}
		return workBook;
	}

	public static ResponseMsg exportAreaResultBill(String excelFilePath,
			AreaResultBillTotalVo areaResultVo) throws Exception {
		// 创建Excel文档
		HSSFWorkbook book = new ExcelExportUtils()
				.getWorkBook(new ExcelExportUtils().billExport);
		Sheet sheet = book.getSheetAt(0);
		// 第一行
		Row row1 = sheet.getRow(0);
		IShareAreaResult areaResultBill = areaResultVo.getShareAreaResult();
		String excelTitle = areaResultBill.getShareName() + " - 分摊域分摊账单";
		Cell cell1 = row1.getCell(0);
		cell1.setCellValue(excelTitle);
		// 第二行
		Row row2 = sheet.getRow(1);
		Cell shareName = row2.getCell(1); // 分摊名称
		shareName.setCellValue(areaResultBill.getShareName());
		Cell belongProject = row2.getCell(3);// 所属项目
		IProject project = areaResultVo.getProject();
		if (project != null) {
			belongProject.setCellValue(project.getProjectName());
		}
		Cell belongCommunity = row2.getCell(5);// 所属小区
		ICommunity community = areaResultVo.getCommunity();
		if (community != null) {
			belongCommunity.setCellValue(community.getCommunityName());
		}
		Cell shareStartTime = row2.getCell(7);// 分摊周期开始日期
		HSSFCellStyle createCellStyle = book.createCellStyle();
		HSSFDataFormat dataFormat = book.createDataFormat();
		short format = dataFormat.getFormat("yyyy-MM-dd");
		createCellStyle.setDataFormat(format);
		shareStartTime.setCellStyle(createCellStyle);
		shareStartTime.setCellValue(areaResultBill.getStartDate());

		Cell shareEndtTime = row2.getCell(9);// 分摊周期结束日期
		shareEndtTime.setCellStyle(createCellStyle);
		shareEndtTime.setCellValue(areaResultBill.getEndDate());
		// 第三行
		Row row3 = sheet.getRow(2);
		Cell userCount = row3.getCell(1);// 总热用户数
		Integer heatRoomSum = areaResultBill.getHeatRoomSum();
		userCount.setCellValue(heatRoomSum);
		Cell notFee = row3.getCell(3);// 不参与记录的热用户数
		notFee.setCellValue(areaResultBill.getNomeasureRoomSum());
		Cell heatArea = row3.getCell(5);// 总供热面积
		heatArea.setCellValue(areaResultBill.getHeatAreaSum());
		// 不参与计量收费的总供热面积
		Cell noheatArea = row3.getCell(7);
		noheatArea.setCellValue(areaResultBill.getNomeasureAreaSum());
		// 第四行
		Row row4 = sheet.getRow(3);
		// 计量热价
		Cell measureChargePricecell = row4.getCell(1);
		String measureChargePrice = Double.toString(areaResultBill
				.getMeasureChargePrice());
		String measureUnit = "";
		if (areaResultBill.getMeasureUnit() == 1) {
			measureUnit = "元/MWh";
		} else if (areaResultBill.getMeasureUnit() == 2) {
			measureUnit = "元/kWh";
		} else if (areaResultBill.getMeasureUnit() == 3) {
			measureUnit = "元/Gj";
		}
		measureChargePrice += " " + measureUnit;
		measureChargePricecell.setCellValue(measureChargePrice);
		// 面积热价
		Cell areaChargeCell = row4.getCell(3);
		String areaChargePrice = Double.toString(areaResultBill
				.getAreaChargePrice()) + " 元/m²";
		areaChargeCell.setCellValue(areaChargePrice);
		// 基本热价
		Cell basePricecell = row4.getCell(5);
		String basePrice = Double.toString(areaResultBill.getBasePrice())
				+ " 元/m²";
		basePricecell.setCellValue(basePrice);
		// 热用户账单列表
		List<AreaResultBillListVo> areaResultBillList = areaResultVo
				.getAreaResultBillList();// 分摊结果
		//
		if (areaResultBillList == null||areaResultBillList.size()<1) {
			// TODO 当没有用户的时候进行一个输出
			OutputStream  excel_outs = new FileOutputStream(excelFilePath);
			ResponseMsg rmsg = ResponseMsg.getInstance(true);
		    try {
		      if ((excel_outs != null) && (book != null))
		      {
		        book.write(excel_outs);
		      }
		      else rmsg = ResponseMsg.getInstance(false, "Excel导出尚未正确配置！"); 
		    }
		    catch (Exception e)
		    {
		      e.printStackTrace();
		      throw new Exception(e.getMessage());
		    } finally {
		      excel_outs.close();
		      excel_outs = null;
		    }
		}
		// 参与分摊的用户
		Predicate inshare = new Predicate() {
			public boolean evaluate(Object object) {
				AreaResultBillListVo areaResultBillListVo = (AreaResultBillListVo) object;
				String ifMeasureCharge = areaResultBillListVo
						.getIfMeasureCharge();
				if ("参与".equals(ifMeasureCharge)) {
					return true;
				}
				return false;
			}
		};
		Collection inshares = CollectionUtils.select(areaResultBillList,
				inshare);
		// 未分摊的用户
		List<AreaResultBillListVo>insharesList = (List<AreaResultBillListVo>)inshares;
		
		int start = 6; // 定义偏移量
		int ini = start;
		for (int i = 0; i < insharesList.size(); i++) {
			AreaResultBillListVo areaResultBillListVo = insharesList.get(i);
			Row row = sheet.createRow(start++);
			Cell  num= row.createCell(0);
			num.setCellValue(start-ini);//序号
			Cell belongBuilding= row.createCell(1);
			belongBuilding.setCellValue(areaResultBillListVo.getBelongBuildingNo());//门牌号
			Cell  houseNoCell= row.createCell(2);
			String houseNo = areaResultBillListVo.getHouseNo();//房间号
			houseNoCell.setCellValue(houseNo);
			Cell owerNameCell = row.createCell(3);
			owerNameCell.setCellValue(areaResultBillListVo.getRoomOwner());//户主名称
			Cell ifMeasure = row.createCell(4);
			ifMeasure.setCellValue(areaResultBillListVo.getIfMeasureCharge()); //是否参与
			Cell basePriceCell = row.createCell(5);
			basePriceCell.setCellValue(areaResultBillListVo.getRoomBaseHeatCharge());//基础热价
			Cell shareFee = row.createCell(6);
			shareFee.setCellValue(areaResultBillListVo.getRoomMeasureCharge());//分摊热价
//			areaResultBill.
			Cell totalPrice = row.createCell(7);
			totalPrice.setCellValue(areaResultBillListVo.getTotalChargeAmount()); //总热价
			Cell areaFee = row.createCell(8);
			areaFee.setCellValue(areaResultBillListVo.getRoomAreaCharge());//面积热费
			Cell caculateMoney = row.createCell(9);
			double fee =areaResultBillListVo.getRoomAreaCharge()-areaResultBillListVo.getTotalChargeAmount();
			caculateMoney.setCellValue(fee); //差额 最后的结果
		}
		//开始未参与的计费
		Collection notInshares = CollectionUtils.selectRejected(areaResultBillList,
				inshare);
		List<AreaResultBillListVo> notInShare = (List<AreaResultBillListVo>) notInshares;
		for (int i = 0; i < notInShare.size(); i++) {
			AreaResultBillListVo areaResultBillListVo = notInShare.get(i);
			Row row = sheet.createRow(start++);
			Cell  num= row.createCell(0);
			num.setCellValue(start-ini);//序号
			Cell belongBuilding= row.createCell(1);
			belongBuilding.setCellValue(areaResultBillListVo.getBelongBuildingNo());//门牌号
			Cell  houseNoCell= row.createCell(2);
			String houseNo = areaResultBillListVo.getHouseNo();
			houseNoCell.setCellValue(houseNo); //房间号
			Cell owerNameCell = row.createCell(3);
			owerNameCell.setCellValue(areaResultBillListVo.getRoomOwner());//户主名称
			Cell ifMeasure = row.createCell(4);
			ifMeasure.setCellValue(areaResultBillListVo.getIfMeasureCharge());
			Cell basePriceCell = row.createCell(5); //基础热价
			basePriceCell.setCellValue(areaResultBillListVo.getRoomBaseHeatCharge());
			Cell shareFee = row.createCell(6);
			shareFee.setCellValue(areaResultBillListVo.getRoomMeasureCharge());//分摊热价
//			areaResultBill.
			Cell totalPrice = row.createCell(7);
			totalPrice.setCellValue(areaResultBillListVo.getTotalChargeAmount()); //总热价
			Cell areaFee = row.createCell(8);
			areaFee.setCellValue(areaResultBillListVo.getRoomAreaCharge());//面积热费
			Cell caculateMoney = row.createCell(9);
			double fee = areaResultBillListVo.getRoomAreaCharge()-areaResultBillListVo.getTotalChargeAmount();
			caculateMoney.setCellValue(fee); //差额 最后的结果
		}
		//以下摘抄自静态代码,明显会有Bug的代码 用静态方法?
		OutputStream  excel_outs = new FileOutputStream(excelFilePath);
		ResponseMsg rmsg = ResponseMsg.getInstance(true);
	    try {
	      if ((excel_outs != null) && (book != null))
	      {
	        book.write(excel_outs);
	      }
	      else rmsg = ResponseMsg.getInstance(false, "Excel导出尚未正确配置！"); 
	    }
	    catch (Exception e)
	    {
	      e.printStackTrace();
	      throw new Exception(e.getMessage());
	    } finally {
	      excel_outs.close();
	      excel_outs = null;
	    }
	    return rmsg;
	}
}